﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;

namespace ece750project
{
   static public  class DBUtility
    {
       public static MySqlConnection DBconn = new MySqlConnection();

        public static   int Login(string user, string pass)
        {
            
            string connectionString = "server=saud.homedns.org;User Id=ece750project;database=abc;password=Database;Persist Security Info=True;Allow User Variables=True; Convert Zero Datetime=true;";
			string this_machine = Environment.MachineName.ToLower();
			
			// || this_machine == "saud-laptop"
            if (this_machine == "saudserver")
            {
                connectionString = "server=192.168.1.56;User Id=ece750project;database=abc;password=Database;Persist Security Info=True;Allow User Variables=True; Convert Zero Datetime=true;";
            }

			try
			{
                if (DBconn.State != ConnectionState.Closed) DBconn.Close();

                DBconn.ConnectionString = connectionString;
                DBconn.Open();
				string parameters = "'"+user+"','"+pass+"'";
				
				int ret = Convert.ToInt32 ( call_func("clientlogin_sp",parameters));
                if (ret == 0) DBconn.Close();
                
				return ret;
			}
			catch(Exception ex)
			{
				MessageBox.Show("Can not connect to the database");
				Debug.Print(ex.Message);
			}
			
            
            return 0;
        }
		
		public static object call_func(string func_name)
		{
			string SQL="call " + func_name + "();";
			return Get_Scalar(SQL);
		}

		public static object call_func(string func_name, params  string[] parameters)
		{
			string SQL="";
			foreach (string item in parameters)
			{
				SQL+=item + ",";
			}
			
			SQL=SQL.Substring(0,SQL.Length-1);
			SQL="call " + func_name + "("+ SQL +");";
			return Get_Scalar(SQL);
		}


		public static DataTable call_sp(string sp_name)
		{
			string SQL="call " + sp_name + "();";
			return Get_Data(SQL);
		}
		
		public static DataTable call_sp(string sp_name,params string[] parameters)
		{
			string SQL="";
			foreach (string item in parameters)
			{
				SQL+=item + ",";
			}
			
			SQL=SQL.Substring(0,SQL.Length-1);
			
			SQL="call " + sp_name + "("+ SQL +");";
			return Get_Data(SQL);
		}
        public static bool ExcuteSQL(string SQL,MySqlCommand pcmd)
        {
            MySqlCommand  cmd;
            if (pcmd!=null)
            {
                cmd = pcmd;
            }
            else
            {
                cmd = new MySqlCommand();
            }
                
            

            //Check SQL string from hackers Injection, Remove any (__) and any (;) from the string
            if (SQL.Contains("__"))
                SQL = SQL.Replace("__", "");
            if (SQL.Contains(";"))
                SQL = SQL.Replace(";", "");
            //____________________________________________________________________________________

            try
            {
                cmd.Connection = DBconn;
                cmd.CommandText = SQL;
                cmd.ExecuteNonQuery();
                return true;

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return false;
        }


        public static object Get_Scalar(string SQL)
        {
            MySqlCommand cmd; 
            //Check SQL string from hackers Injection, Remove any (__) and any (;) from the string
            if (SQL.Contains("__"))
                SQL = SQL.Replace("__", "");
            if (SQL.Contains(";"))
                SQL = SQL.Replace(";", "");
            //____________________________________________________________________________________

            try
            {
                cmd = new MySqlCommand(SQL, DBconn);
                return cmd.ExecuteScalar();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


            return null;
        }


        public static DataTable Get_Data(string SQL)
        {
            //Check SQL string from hackers Injection, Remove any (__) and any (;) from the string
            if (SQL.Contains("__"))
                SQL = SQL.Replace("__", "");
            if (SQL.Contains(";"))
                SQL = SQL.Replace(";", "");
            //____________________________________________________________________________________

            MySqlDataAdapter DA;
            DataTable DT;

            try
            {
                DA = new MySqlDataAdapter(SQL, DBconn);
                DT = new DataTable();

                DA.Fill(DT);

                DA.Dispose();
                DA = null;

                return DT;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

                
        }


        public static DataTable Get_Data(string SQL,MySqlCommand pcmd)
        {
            MySqlCommand cmd;
            if (pcmd != null)
            {
                cmd = pcmd;
            }
            else
            {
                cmd = new MySqlCommand();
            }
                

            //Check SQL string from hackers Injection, Remove any (__) and any (;) from the string
            if (SQL.Contains("__"))
                SQL = SQL.Replace("__", "");
            if (SQL.Contains(";"))
                SQL = SQL.Replace(";", "");
            //____________________________________________________________________________________

            MySqlDataAdapter DA;
            DataTable DT;

            try
            {
                cmd.CommandText = SQL;
                cmd.Connection = DBconn;
                DA = new MySqlDataAdapter(cmd);
                DT = new DataTable();

                DA.Fill(DT);

                DA.Dispose();
                DA = null;

                return DT;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }


        }
    

        public static bool  Set_Session_Var(string VarName, string Value)
        {
            string SQL = "SET @" + VarName + "=" + Value+";";
            try
            {
                return ExcuteSQL(SQL,null);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }

        }

     

        public static long Get_MAX_ID(string TableName, string FieldName)
        {
            DataTable DT = null;
            string SQL = null;
            try
            {
                SQL = "Select Max(" + FieldName + ") as MaxID From " + TableName + "";
                DT = Get_Data(SQL);
                if (DT.Rows.Count > 0)
                {
                    if (DT.Rows[0]["MaxID"] == null)
                    {
                        return Convert.ToInt32( DT.Rows[0]["MaxID"]);
                    }
                    else
                    {
                        return 0;
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return -1;
            }
            return -1;
        }


        public static string Get_Session_Var(string VarName)
        {

            string SQL = "Select @" + VarName + " as " + VarName;
            try
            {
                object Res=null;
                Res= Get_Scalar(SQL);
                if (Res != null)
                    return Convert.ToString(Res);
                else
                    return "";
            }
            
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return "";
            }
            
        }

    }
}
